import pymysql
from dj030201.settings import *
import django.http

class search():
    def __init__(self):
        self.saleslist = []
        self.detailList = []
        self.detailList_top = []
        self.searchList = []
        pass

    def search_saleslist(self):
        mysql_db = pymysql.connect(host=DB_HOST,user=DB_USER,password=DB_PASSWORD,db=DB_NAME)
        cursor = mysql_db.cursor()
        sql = 'SELECT SerialNumber,TotalNumber,TotalPrice,ReceiveMoney,ReturnMoney,UserName,BuyTime FROM SalesList As a Left Outer Join Login As b on a.LoginId = b.LoginId Order by BuyTime DESC '
        try:
            cursor.execute(sql)
            self.saleslist = cursor.fetchall()
        except Exception as e:
            django.http.HttpResponse('数据库出现异常：{}'.format(e))
        finally:
            mysql_db.close()

    def search_detailList(self,serialNumber):
        '''
        清单详细页面的货物列表
        :param serialNumber:
        :return:
        '''
        self.detailList = []
        self.detailList_top = []
        mysql_db = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, db=DB_NAME)
        cursor = mysql_db.cursor()
        # 下面的详细清单
        sql1 = 'SELECT SerialNumber,ProductId,ProductName,Unit,UnitPrice,Number,Money from saleslistdetail where SerialNumber = {}'.format(serialNumber)
        # 上面的总体清单
        sql2 = 'SELECT SerialNumber,TotalNumber,TotalPrice,UserName,BuyTime FROM SalesList As a Left Outer Join Login As b on a.LoginId = b.LoginId where SerialNumber = {}'.format(
            serialNumber)
        try:
            cursor.execute(sql1)
            self.detailList = cursor.fetchall()
            cursor.execute(sql2)
            self.detailList_top = cursor.fetchone()
        except Exception as e:
            django.http.HttpResponse('数据库出现异常：{}'.format(e))
        finally:
            mysql_db.close()

    def delete_salesList(self,serialNum):
        mysql_db = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, db=DB_NAME)
        cursor = mysql_db.cursor()
        sql2 = 'delete from SalesList where SerialNumber = {}'.format(serialNum)
        try:
            cursor.execute(sql2)
            mysql_db.commit()
        except Exception as e:
            mysql_db.rollback()
            django.http.HttpResponse('数据库出现异常：{}'.format(e))
        finally:
            mysql_db.close()

    def search_lists(self,query):
        '''
        通过查询获得指定的列表
        :return:
        '''
        self.searchList = []
        mysql_db = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, db=DB_NAME)
        cursor = mysql_db.cursor()
        sql = 'Select SerialNumber,TotalNumber,TotalPrice,ReceiveMoney,ReturnMoney,Username,BuyTime ' \
               'from SalesList As T1 Left Outer Join Login As T2 on T1.LoginId = T2.LoginId ' \
               'where SerialNumber like "{}" and UserName like "{}" and BuyTime Between "{}" and "{}" ' \
               'Order By BuyTime DESC'.format('%' + query['serialNum'] + '%','%' + query['username'] + '%',query['start'],query['end'])
        try:
            cursor.execute(sql)
            self.searchList = cursor.fetchall()
            print(self.searchList)
        except Exception as e:
            django.http.HttpResponse('数据库出现异常：{}'.format(e))
        finally:
            mysql_db.close()



if __name__ == '__main__':
    s = search()
    s.search_detailList(202104231507218402)
    print('删除成功')